const express = require("express");
const router = express.Router();
const mysqlUtil = require('../utils/mysqlUtil');

// 分页查询
router.get('/page', async (req, res) => {
    const page = parseInt(req.query.page) || 1;
    const pageSize = parseInt(req.query.pageSize) || 10;
    const offset = (page - 1) * pageSize;

    try {
        // 动态构建SQL语句
        let totalSql = 'SELECT COUNT(*) AS total FROM book';
        let listSql = 'SELECT * FROM book';

        // 获取总记录数
        const totalResult = await mysqlUtil.query(totalSql);
        const total = totalResult[0].total;

        // 分页查询列表
        const result = await mysqlUtil.query(`${listSql} LIMIT ? OFFSET ?`, [pageSize, offset]);

        res.json({
            status: true,
            message: '查询成功',
            data: result,
            page: page,
            pageSize: pageSize,
            total: total,
            code: '200'
        });
    } catch (error) {
        console.error(error); // 记录错误信息
        res.status(500).json({
            status: false,
            message: '查询失败',
            error: error.message,
            code: '500'
        });
    }
});

// 查询所有
router.get('/all', async (req, res) => {
    try {
        const result = await mysqlUtil.query('SELECT * FROM book');
        res.json({
            status: true,
            message: '查询成功',
            data: result,
            code: '200'
        });
    } catch (error) {
        console.error(error);
        res.status(500).json({
            status: false,
            message: '服务器内部错误',
            error: error.message
        });
    }
});

//新增
router.post('/add', async (req, res) => {
    const { imgs, title, type, content, cover, status, taste, description, video } = req.body;

    // 构建要插入的字段名和值数组
    const fields = [];
    const values = [];

    if (imgs !== undefined && imgs !== null) {
        fields.push('imgs');
        values.push(imgs);
    }
    if (title !== undefined && title !== null) {
        fields.push('title');
        values.push(title);
    }
    if (type !== undefined && type !== null) {
        fields.push('type');
        values.push(type);
    }
    if (content !== undefined && content !== null) {
        fields.push('content');
        values.push(content);
    }
    if (cover !== undefined && cover !== null) {
        fields.push('cover');
        values.push(cover);
    }
    if (status !== undefined && status !== null) {
        fields.push('status');
        values.push(status);
    }
    if (taste !== undefined && taste !== null) {
        fields.push('taste');
        values.push(taste);
    }
    if (video !== undefined && video !== null) {
        fields.push('video');
        values.push(video);
    }
    if (description !== undefined && description !== null) {
        fields.push('description');
        values.push(description);
    }

    // 如果没有任何字段提供，则返回错误信息
    if (fields.length === 0) {
        return res.status(400).json({
            status: false,
            message: '至少需要提供一个字段'
        });
    }

    // 动态构建 SQL 插入语句
    const sql = `INSERT INTO book (${fields.join(', ')}) VALUES (${fields.map(() => '?').join(', ')})`;

    try {
        const result = await mysqlUtil.query(sql, values);
        res.json({
            status: true,
            message: '新增成功',
            data: {
                id: result.insertId
            },
            code: '200'
        });
    } catch (error) {
        console.error(error); // 记录错误日志以便调试
        res.status(500).json({
            status: false,
            message: '服务器内部错误',
            error: error.message
        });
    }
});

// 删除指定ID的
router.delete('/delete/:id', async (req, res) => {
    const id = req.params.id;

    // 检查 id 是否有效
    if (!id || isNaN(id)) {
        return res.status(400).json({
            status: false,
            message: '无效的书籍ID'
        });
    }

    try {
        const result = await mysqlUtil.query('DELETE FROM book WHERE id = ?', [id]);
        if (result.affectedRows === 0) {
            return res.status(404).json({
                status: false,
                message: '未找到要删除的数据'
            });
        }
        res.json({
            status: true,
            message: '删除成功',
            data: {
                affectedRows: result.affectedRows
            },
            code: '200'
        });
    } catch (error) {
        console.error(error);
        res.status(500).json({
            status: false,
            message: '服务器内部错误',
            error: error.message
        });
    }
});

//修改
router.put('/update', async (req, res) => {
    const { id, imgs, title, type, content, cover, status, taste, description, video } = req.body;

    // 检查 id 是否存在
    if (!id) {
        return res.status(400).json({
            status: false,
            message: '书籍 ID 不能为空'
        });
    }

    // 构建更新字段
    const updateFields = [];
    const values = [];

    if (imgs !== undefined && imgs !== null) {
        updateFields.push('imgs = ?');
        values.push(imgs);
    }
    if (title !== undefined && title !== null) {
        updateFields.push('title = ?');
        values.push(title);
    }
    if (type !== undefined && type !== null) {
        updateFields.push('type = ?');
        values.push(type);
    }
    if (content !== undefined && content !== null) {
        updateFields.push('content = ?');
        values.push(content);
    }
    if (description !== undefined && description !== null) {
        updateFields.push('description = ?');
        values.push(description);
    }
    if (video !== undefined && video !== null) {
        updateFields.push('video = ?');
        values.push(video);
    }
    if (cover !== undefined && cover !== null) {
        updateFields.push('cover = ?');
        values.push(cover);
    }
    if (status !== undefined && status !== null) {
        updateFields.push('status = ?');
        values.push(status);
    }
    if (taste !== undefined && taste !== null) {
        updateFields.push('taste = ?');
        values.push(taste);
    }

    // 如果没有要更新的字段，直接返回成功
    if (updateFields.length === 0) {
        return res.json({
            status: true,
            message: '无需更新任何字段'
        });
    }

    // 构建 SQL 查询
    const sql = `UPDATE book SET ${updateFields.join(', ')} WHERE id = ?`;
    values.push(id);

    try {
        const result = await mysqlUtil.query(sql, values);
        if (result.affectedRows === 0) {
            return res.status(404).json({
                status: false,
                message: '未找到要更新的数据'
            });
        }
        res.json({
            status: true,
            message: '修改成功',
            data: {
                affectedRows: result.affectedRows
            },
            code: '200'
        });
    } catch (error) {
        console.error(error); // 记录错误日志以便调试
        res.status(500).json({
            status: false,
            message: '服务器内部错误',
            error: error.message
        });
    }
});

module.exports = router;